# --------------------------------------------------------         获取到MYSQL数据库的链接对象
from pymysql import Connection
conn = Connection(
    host='localhost',          # 主机名（或IP地址）
    port=3306,                 # 端口，默认3306
    user='root',               # 账户名
    password='123456',      # 密码
    autocommit=True,            # 设置自动提交（确认）
    database='mydb',
    charset='utf8'
)
'''
或：
import pymysql
conn = pymysql.connect(......)
'''
print(conn.get_server_info())

# --------------------------------------------------------          执行SQL语句
# 获取游标对象
cursor = conn.cursor()  # 游标主要是用来遍历结果集的
# 先选择数据库
conn.select_db("world")
# --------- 创建表
cursor.execute("create table test_pymysql(id int, info varchar(255))")

row_count = cursor.execute("select * from student");
print(f"(sql语句执行影响的行数{row_count})");

# results = cursor.fetchone() 获取第一条数据，数据为元组形式
# 获取所有数据
results = cursor.fetchall()
for r in results:
    print(r)
# 或者
for r1 in cursor.fechall():
    print(r1)

# ---------增
cursor.execute("insert into student values(1,'周杰',22)")
# ---------删
cursor.execute("delete from student where name = '周杰'")
# ---------改
cursor.execute("alter table student set id = 2 where name = '周杰'")

# 优化：（%s不加引号，且使用cursor.execute(sql1, data1)的形式，可以防止sql注入）
# sql注入：无论怎么查询，都会出现结果，造成信息泄露
# 增
sql1 = "insert into student values(%s,%s,%s)"
data1 = (4, '张三', 21)
cursor.execute(sql1, data1)
# 删
sql2 = "delete from student where name = %s"
data2 = ('周杰')
cursor.execute(sql2, data2)
# 改
sql3 = "alter table student set age = %s where id = %s"
data3 = (23, 3)
cursor.execute(sql3, data3)

# 确认更改行为
conn.commit()

# 先关闭游标，再关闭连接
cursor.close()
conn.close()



